package com.google.bitcoin.db;


import com.google.bitcoin.core.*;

import java.util.List;
import java.util.LinkedList;
import java.util.Collection;
import java.util.HashSet;
import java.util.HashMap;
import java.util.Set;

import java.math.BigInteger;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.ThreadFactory;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.Semaphore;

import java.util.Properties;
import java.util.Date;
import java.util.TimeZone;
import java.text.SimpleDateFormat;


import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.DriverManager;


public class SqlDB extends TransactionDB
{
    private static final Logger log = LoggerFactory.getLogger(SqlDB.class);

    private NetworkParameters params;
    private BlockChainCache block_chain_cache;
    private String sql_pool_name;
    private LRUCache<Sha256Hash, Transaction> tx_cache;

    public SqlDB(NetworkParameters params, BlockChainCache block_chain_cache, String sql_pool_name)
    {   
        this.params=params;
        this.block_chain_cache=block_chain_cache;
        this.sql_pool_name=sql_pool_name;
        tx_cache = new LRUCache<Sha256Hash, Transaction>(4096);



    }

    @Override
    public void clearCache()
    {
        synchronized(tx_cache)
        {
            tx_cache.clear();
        }
    }
    public Transaction getTransaction(Sha256Hash hash)
    {
        Connection conn=null;
        try
        {
            conn=openConnection();
            return getTransaction(hash, conn);

        }
        catch(SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            closeConnection(conn);
        }

    }

    public Transaction getTransaction(Sha256Hash hash, Connection conn)
        throws SQLException
    {
        PreparedStatement ps = conn.prepareStatement("select * from wallet_transactions where tx=?");
        ps.setString(1, hash.toString());
        ResultSet r = ps.executeQuery();
        if (r.next())
        {
            return loadTransaction(r, conn);

                               
        }

        r.close();
        ps.close();

        return null;
        
    }

    public Transaction getTransactionCached(Sha256Hash hash, Connection conn)
        throws SQLException
    {
        synchronized(tx_cache)
        {
            if (tx_cache.containsKey(hash)) return tx_cache.get(hash);
        }
        Transaction tx = getTransaction(hash, conn);
        synchronized(tx_cache)
        {
            tx_cache.put(hash, tx);
        }
        return tx;
        
    }

    public void clearCache(Sha256Hash hash)
    {
        synchronized(tx_cache)
        {
            tx_cache.remove(hash);
        }
    }

    private Transaction loadTransaction(ResultSet r, Connection conn)
        throws SQLException
    {
        try
        {
            Transaction tx = new Transaction(params, r.getBytes("bytes"));
            loadSpends(tx, conn);
            loadConfidence(tx, conn);
            long time = r.getLong("stamp");
            tx.setUpdateTime(new Date(time));
            return tx;
        }
        catch(com.google.bitcoin.core.ProtocolException e)
        {
            throw new RuntimeException(e);
        }
    }
    private void loadSpends(Transaction tx, Connection conn)
        throws SQLException
    {
        PreparedStatement ps = conn.prepareStatement("select * from wallet_spends where from_tx=?");
        ps.setString(1, tx.getHash().toString());
        ResultSet r = ps.executeQuery();
        while(r.next())
        {
            int out_idx = r.getInt("from_idx");

            Sha256Hash to_tx = new Sha256Hash(r.getString("to_tx"));
            int to_idx = r.getInt("to_idx");
            TransactionOutPoint out_point = new TransactionOutPoint(params, to_idx, to_tx);

            tx.getOutputs().get(out_idx).addSpentByInput(out_point);

        }

        r.close();
        ps.close();

    }
    private void loadConfidence(Transaction tx, Connection conn)
        throws SQLException
    {
        PreparedStatement ps = conn.prepareStatement("select * from wallet_seen_tx where tx=?");
        ps.setString(1, tx.getHash().toString());
        ResultSet r = ps.executeQuery();
        while(r.next())
        {
            Sha256Hash block = new Sha256Hash(r.getString("block"));
            tx.addBlockAppearance(block);
        }
        r.close();
        ps.close();

        int depth=block_chain_cache.getDepth(tx.getAppearsInHashes());
        if (depth==0)
        {
            if ((tx.getAppearsInHashes() != null) && (tx.getAppearsInHashes().size()>0))
            {
                tx.getConfidence().setConfidenceType(TransactionConfidence.ConfidenceType.NOT_IN_BEST_CHAIN );
                int height = block_chain_cache.getHeight(tx.getAppearsInHashes());
                tx.getConfidence().setAppearedAtChainHeight(height);
            }
            else
            {
                tx.getConfidence().setConfidenceType(TransactionConfidence.ConfidenceType.NOT_SEEN_IN_CHAIN );
            }
        }
        if (depth>0)
        {
            tx.getConfidence().setConfidenceType(TransactionConfidence.ConfidenceType.BUILDING );
            int height = block_chain_cache.getHeight(tx.getAppearsInHashes());
            tx.getConfidence().setAppearedAtChainHeight(height);
        }

    }
    
    public Transaction getTransaction(Sha256Hash hash, int connected_out_levels)
    {
        Transaction tx = getTransaction(hash);
        if (connected_out_levels <= 0) return tx;
        if (tx==null) return tx;

        for(TransactionOutput out : tx.getOutputs())
        {
            if ((out.getSpentByInputs()!=null)&& (out.getSpentByInputs().size()>0))
            {
                TransactionOutPoint in = out.getSpentByInputs().iterator().next();
                Transaction child=getTransaction(in.getHash(), connected_out_levels-1);
                out.markAsSpent(child.getInputs().get((int)in.getIndex()));

            }

        }
        return tx;
    }

    public Collection<TransactionOutput> getUnspentOutputs(Address addr)
    {
        LinkedList<TransactionOutput> lst = new LinkedList<TransactionOutput>();
        Connection conn=null;
        try
        {
            conn=openConnection();
 
            PreparedStatement ps = conn.prepareStatement("select * from wallet_outputs where address=? and spent_count=0");
            ps.setString(1, addr.toString());
            ResultSet r = ps.executeQuery();
            while(r.next())
            {
                Sha256Hash hash = new Sha256Hash(r.getString("tx"));
                int idx=r.getInt("idx");
                Transaction tx=getTransactionCached(hash, conn);
                lst.add(tx.getOutputs().get(idx));
    
            }

        }
        catch(SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            closeConnection(conn);
        }


        return lst;
    }
    public Collection<TransactionOutput> getUnspentOutputs(Address addr, BigInteger min_output)
    {
        LinkedList<TransactionOutput> lst = new LinkedList<TransactionOutput>();
        Connection conn=null;
        try
        {
            conn=openConnection();
 
            PreparedStatement ps = conn.prepareStatement("select * from wallet_outputs where address=? and spent_count=0 and value >= ?");
            ps.setString(1, addr.toString());
            ps.setLong(2, min_output.longValue());
            ResultSet r = ps.executeQuery();
            while(r.next())
            {
                Sha256Hash hash = new Sha256Hash(r.getString("tx"));
                int idx=r.getInt("idx");
                Transaction tx=getTransactionCached(hash, conn);
                lst.add(tx.getOutputs().get(idx));
    
            }

        }
        catch(SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            closeConnection(conn);
        }


        return lst;
    }
 
    public void saveTx(Transaction tx, boolean allow_double_spend) throws VerificationException, DoubleSpendException
    {
        while(true)
        {
            try
            {
                saveTxOnce(tx, allow_double_spend);
                clearCache(tx.getHash());
                return;

            }
            catch(SQLException e)
            {
                log.error("SQL Error on save, retrying", e);
            }

        }

    }

    private void saveTxOnce(Transaction tx, boolean allow_double_spend) throws VerificationException, DoubleSpendException, SQLException
    {
        clearCache(tx.getHash());

        Connection conn=null;
        try
        {
            conn=openConnection();

            conn.setAutoCommit(false);
            //Insert transaction
            {
                Date update_time = tx.getUpdateTime();
                if (update_time.getTime()==0L)
                {
                    update_time=new Date();
                }
            
                PreparedStatement ps = conn.prepareStatement("insert ignore into wallet_transactions (tx, first_seen, bytes, stamp) values (?,?,?,?)");
                ps.setString(1, tx.getHash().toString());
                ps.setString(2, getGMTDateTime(update_time));
                ps.setBytes(3, tx.bitcoinSerialize());
                ps.setLong(4, update_time.getTime());
                ps.execute();
                ps.close();
            }

            //Insert any seens
            {
                if (tx.getAppearsInHashes()!=null)
                {
                    for(Sha256Hash block : tx.getAppearsInHashes())
                    {
                        PreparedStatement ps = conn.prepareStatement("insert ignore into wallet_seen_tx (tx, block) values (?,?)");
                        ps.setString(1, tx.getHash().toString());
                        ps.setString(2, block.toString());
                        ps.execute();
                        ps.close();

                    }
                }
            }
            //Insert spends
            {
                for(TransactionInput input : tx.getInputs())
                {
                    TransactionOutPoint out = input.getOutpoint();

                    if (!allow_double_spend)
                    {
                        PreparedStatement ps = conn.prepareStatement("select * from wallet_spends where from_tx=? and from_idx=?");
                        ps.setString(1, out.getHash().toString());
                        ps.setInt(2, (int)out.getIndex());
                        ResultSet r = ps.executeQuery();

                        while(r.next())
                        {
                            Sha256Hash to_tx = new Sha256Hash(r.getString("to_tx"));
                            int to_idx = r.getInt("to_idx");
                            if ((!to_tx.equals(tx.getHash())) || (to_idx != tx.getInputs().indexOf(input)))
                            {
                                conn.rollback();
                                throw new DoubleSpendException("Double spend on " + out);
                            }

                        }

                        r.close();
                        ps.close();

                    }

                    PreparedStatement ps = conn.prepareStatement("insert ignore into wallet_spends (from_tx, from_idx, to_tx, to_idx) values (?,?,?,?)");
                    ps.setString(1, out.getHash().toString());
                    ps.setInt(2, (int)out.getIndex());
                    ps.setString(3, tx.getHash().toString());
                    ps.setInt(4, tx.getInputs().indexOf(input));

                    ps.execute();
                    ps.close();
                    updateSpendCount(out, conn);
                    clearCache(out.getHash());


                }


            }
            //Insert Outputs
            for(TransactionOutput out : tx.getOutputs())
            {
                try
                {
                    PreparedStatement ps = conn.prepareStatement("insert ignore into wallet_outputs (tx, idx, address, value, spent_count) values(?,?,?,?,(select count(*) from wallet_spends where from_tx=? and from_idx=?))");
                    int idx=tx.getOutputs().indexOf(out);
                    ps.setString(1, tx.getHash().toString());
                    ps.setInt(2, idx);
                    ps.setString(3, out.getScriptPubKey().getToAddress().toString());
                    ps.setLong(4, out.getValue().longValue());
                    ps.setString(5, tx.getHash().toString());
                    ps.setInt(6, idx);
                    ps.execute();
                    ps.close();
                }
                catch(com.google.bitcoin.core.ScriptException e)
                {
                    log.error("Unable to save transaction output, no address: " + tx.getHash() + ":" + tx.getOutputs().indexOf(out));

                }
            }


            //Commit
            conn.commit();





        }
        finally
        {
            closeConnection(conn);
        }

    }

    private void updateSpendCount(TransactionOutPoint out, Connection conn)
        throws SQLException
    {
        PreparedStatement ps = conn.prepareStatement("update wallet_outputs set spent_count=(select count(*) from wallet_spends where from_tx=tx and from_idx=idx) where tx=? and idx=?");
        ps.setString(1, out.getHash().toString());
        ps.setInt(2, (int)out.getIndex());
        ps.execute();
        ps.close();
        
    }
    public void removeTx(Transaction tx) throws com.google.bitcoin.core.ScriptException
    {
        if (tx==null) throw new RuntimeException("Tried to remove null transaction");

        Connection conn=null;
        try
        {
            conn=openConnection();
            //Remove transaction
            {
                PreparedStatement ps = conn.prepareStatement("delete from wallet_transactions where tx=?");
                ps.setString(1,tx.getHash().toString());
                ps.execute();
                ps.close();
            }
            //Remove outputs
            {
                PreparedStatement ps = conn.prepareStatement("delete from wallet_outputs where tx=?");
                ps.setString(1,tx.getHash().toString());
                ps.execute();
                ps.close();

            }
            //Remove spent
            {
                PreparedStatement ps = conn.prepareStatement("delete from wallet_spends where to_tx=?");
                ps.setString(1,tx.getHash().toString());
                ps.execute();
                ps.close();
            }
            //Update spent counts
            for(TransactionInput in : tx.getInputs())
            {
                TransactionOutPoint out = in.getOutpoint();
                updateSpendCount(out, conn);
            }

            //Commit
            conn.commit();

        }
        catch(SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            closeConnection(conn);
        }


    }
    public  Set<Transaction> getTransactions(boolean includeDead, boolean includeInactive)
    {
        HashSet<Transaction> set=new HashSet<Transaction>();

        Connection conn=null;
        try
        {
            conn=openConnection();

            PreparedStatement ps = conn.prepareStatement("select * from wallet_transactions");
            ResultSet r = ps.executeQuery();
            while (r.next())
            {
                set.add(loadTransaction(r, conn));

                               
            }

            r.close();
            ps.close();


        }
        catch(SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            closeConnection(conn);
        }
        return set;


    }
    public Set<Transaction> getPending()
    {
        HashSet<Transaction> set=new HashSet<Transaction>();

        Connection conn=null;
        try
        {
            conn=openConnection();
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

            PreparedStatement ps = conn.prepareStatement("select * from wallet_transactions where (select count(*) from wallet_seen_tx where wallet_transactions.tx=tx)=0");
            ResultSet r = ps.executeQuery();
            while (r.next())
            {
                set.add(loadTransaction(r, conn));

                               
            }

            r.close();
            ps.close();


        }
        catch(SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            closeConnection(conn);
        }
        return set;


    }


    @Override
    public BigInteger getBalance(BalanceType balanceType, Address addr)
    {
        if (balanceType!=BalanceType.ESTIMATED)
        {
            return super.getBalance(balanceType, addr);
        }

        Connection conn=null;
        try
        {
            conn=openConnection();

            PreparedStatement ps = conn.prepareStatement("select sum(value) as sum from wallet_outputs where address=? and spent_count =0");
            ps.setString(1, addr.toString());
            ResultSet r = ps.executeQuery();
            r.next();
            BigInteger value = BigInteger.valueOf(r.getLong("sum"));

            r.close();
            ps.close();
            return value;


        }
        catch(SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            closeConnection(conn);
        }

    }


    private Connection openConnection()
        throws SQLException
    {
        Connection conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + sql_pool_name);
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        return conn;

    }
    private void closeConnection(Connection conn)
    {
        if (conn==null) return;
        try
        {
            conn.rollback();
            conn.close();
        }
        catch(SQLException e)
        {}
    }

    public static String getGMTDateTime(Date d)
    {
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
        return sdf.format(d);
    }


}

